-- STANDARD SCRIPT
-- SCRIPT TO PREVENT THE SAME DATA TO BE RUN TWICE 
--        TO PREVENT PART 1 TO BE RAN WHEN THE RESULTS OF PART 2 FROM PREVIOUS WEEK HAVE NOT BEEN LOADED


\set ON_ERROR_STOP 1
CREATE OR REPLACE FUNCTION audit_insurance1_f() RETURNS integer AS $$
DECLARE
  v_status integer := 0;
  v_batch_run_date timestamp;
  v_cust_id integer;
  v_batch_id integer;
  v_prior_max_trans_date timestamp;
  v_processing_type varchar(1);
  v_customer_type varchar(1);
  v_recon_ind varchar(1);
  v_recon_exclude varchar(1);
  v_recon_full_scenario varchar(1);
  v_audit_process varchar(70);
BEGIN



-- STEP 1: OBTAIN THE DATE OF THE CURRENT RUNNING BATCH 
--         OBTAIN THE cust_id


  SELECT INTO v_batch_run_date,v_cust_id our_run_date,cust_id FROM  batch_insurance1_t limit 1;



-- STEP 2: OBTAIN THE DATE OF THE LAST BATCH RUN FROM batch_status_insurance1_t
--         IF THE BATCH WAS ALREADY RUN v_status is ASSIGNED THE VALUE 1 AN ERROR MESSAGE IS WRITTEN
--         IF NOT THEN A NEW ROW IS CREATED IN batch_status_insurance1_t

  SELECT INTO v_prior_max_trans_date 		max(last_run_date) 		FROM batch_status_insurance1_t WHERE cust_id = v_cust_id;
  SELECT INTO v_audit_process        		audit_process      		FROM batch_status_insurance1_t WHERE last_run_date = v_prior_max_trans_date;
  SELECT INTO v_batch_id             		max(batch_id)      		FROM batch_status_insurance1_t ;
  SELECT INTO v_processing_type,v_customer_type processing_type,customer_type   FROM insurance0_treatment_t    WHERE cust_id = v_cust_id LIMIT 1;
  SELECT INTO v_recon_ind,v_recon_exclude  	recon_ind,recon_exclude 	        FROM insurance0_treatment_t    WHERE cust_id = v_cust_id LIMIT 1;
  SELECT INTO v_recon_full_scenario             recon_full_scenario             FROM insurance0_treatment_t    WHERE cust_id = v_cust_id LIMIT 1;


  IF    v_prior_max_trans_date = v_batch_run_date                                          THEN v_status := 1;
  ELSIF v_prior_max_trans_date IS NOT NULL and v_audit_process <> 'BATCH DONE'             THEN v_status := 2;
  ELSE  v_status := 0;
  END IF;

  IF (v_status = 0) THEN

    IF (v_batch_id IS NULL) THEN
        v_batch_id := 0;
    END IF;

    v_batch_id := v_batch_id + 1;

    INSERT INTO batch_status_insurance1_t
      (batch_id,last_run_date,cust_id,processing_type,customer_type,recon_ind,recon_exclude,recon_full_scenario,audit_part_id,audit_step_id,audit_process)
    VALUES
      (v_batch_id,v_batch_run_date,v_cust_id,v_processing_type,v_customer_type,v_recon_ind,v_recon_exclude,v_recon_full_scenario,1,1,'NEW ROW');

  ELSIF (v_status = 2) THEN 
      RAISE EXCEPTION 'AUDIT ERROR IN batch_status_insurance1_t.  PART 2 OF PREVIOUS RUN NOT LOADED';
      v_status := 0;
  ELSE
      RAISE EXCEPTION 'AUDIT ERROR IN batch_status_insurance1_t:THIS BATCH WAS ALREADY RUN. DELETE THIS ROW IF YOU STILL WANT TO PROCEED';
      v_status := 0;
  END IF;

  RETURN v_status;
END;
$$ LANGUAGE plpgsql;
